﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace ch01
{
    class DBHelper
    {
        public  const string CONNSTR = "server=.;database=myschool1;uid=sa;pwd=sa";
        private static SqlConnection _conn;
        public static SqlConnection conn
        {
            get
            {
                if (_conn==null)
                {
                    _conn = new SqlConnection(CONNSTR);
                }
                if (_conn.State==ConnectionState.Broken)
                {
                    _conn.Close();
                }
                if (_conn.State==ConnectionState.Closed)
                {
                    _conn.Open();
                }
                return _conn;
            }
        }
        public static object SelectOne(string sql)
        {
            SqlCommand comm = null;
            object o = null;
            try
            {
                comm = new SqlCommand(sql, conn);
                o = comm.ExecuteScalar();
            }
            catch (Exception e)
            {
                Console.WriteLine("数据库链接异常：" + e);
            }
            finally
            {
                comm.Connection.Close();
            }
            return o;
        }
        public static int DataUpdate(string sql)
        {
            SqlCommand comm = null;
            int reader = -1;
            try
            {
                comm = new SqlCommand(sql, conn);
                reader = comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("数据库链接异常：" + e);
            }
            finally
            {
                comm.Connection.Close();
            }
            return reader;
        }
        public static SqlDataReader SelectMore(string sql)
        {
            SqlDataReader reader = null;
            try
            {
                SqlCommand comm = new SqlCommand(sql,conn);
                reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception e)
            {
                 Console.WriteLine("数据库链接异常：" + e);
            }
            return reader;
        }

        public static DataTable SelectTable(string sql)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql,conn);
                da.Fill(dt);
            }
            catch (Exception e)
            {
                System.Windows.Forms.MessageBox.Show("数据库连接异常：" + e);
            }
            return dt; 
        }
       
    }
}
